package cn.gz.zoomtech.common.db;

import java.sql.Connection;

import java.sql.PreparedStatement;
import java.sql.ResultSet;

import java.util.ArrayList;
import java.util.List;



import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import cn.gz.lbs.entity.CmsUser;
import cn.gz.lbs.entity.TLbsHistory;



public class DbHandle {
  private static final Log log = LogFactory.getLog(DbHandle.class);
//  private CompositeConfiguration config = ConfManager.getInstance().getConf();
//  private SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
  private DBManager dbManager = null;
  
  public DbHandle() {
	  
  }
  
  
    
	public static void main(String[] args) {
		System.out.println("***整理数据begin***");
		DbHandle dbh = new DbHandle();
		
		System.out.println("***整理数据end***");

		
	}
	
	/**
	 * 
	* package_name: cn.gz.zoomtech.common.db
	* file_name:    DbHandle.java
	* description: java.util.Date类型转换成java.sql.Date类型
	* 2013-7-9下午4:50:21
	* Author: chenhui
	 * @param date
	 * @return
	 */
	public static java.sql.Date getSqlDateTime(java.util.Date date){
		java.sql.Date sqlDate = null ;
		try{
			long time = date.getTime();
	        sqlDate = new java.sql.Date(time);
		}catch(Exception ex){
			log.error("javaDateError:",ex);
		}
        return   sqlDate;   
   } 


  /**
   * 
  * package_name: cn.gz.zoomtech.common.db
  * file_name:    DbHandle.java
  * description:  扫描基站数据，返回基站原始经纬度信息等
  * 2011-11-10下午12:44:46
  * Author: chenhui
   * @param lac 基站位置区标识
   * @param cellid 基站ID
   * @param  type 1:扫t_cell_gsm_gx表；其它：扫t_cell_wcdma_gx表
   * @return
   */
//  public CellInfo scanCellXY(String lac,String cellid,int type){
//	  
// 
//	    CellInfo entity = null;
//	    Connection conn = null;
//		PreparedStatement pstmt = null;
//		ResultSet rs = null;
//		int count = 0;
//		try {
//			
//			dbManager = DBManager.getInstance();
//			conn = dbManager.getConnection("DBPool");
//			if (conn == null) {
//				log.error("can't get connection DBPool");
//				return null;
//			} else {
//				String sql = "";
//				if(type==1){
//					sql = " select * from t_cell_gsm_gx t " +
//			       	 " where t.cell_lac='"+lac+"' and t.cell_ci='"+cellid+"' ";
//				}else{
//					sql = " select * from t_cell_wcdma_gx t " +
//			       	 " where t.cell_lac='"+lac+"' and t.cell_ci='"+cellid+"' ";
//				}
//				
//				log.info("sql:" + sql);
//				pstmt = conn.prepareStatement(sql);
//				rs = pstmt.executeQuery();
//			    while (rs.next()) {
//			    	count++;
//			    	if(count>=2){
//			    		break;
//			    	}
//			    	entity = new CellInfo();
//			    	entity.setLac(rs.getString("cell_lac"));
//			    	entity.setCellid(rs.getString("cell_ci"));
//			    	entity.setCellx(rs.getString("cellx"));
//			    	entity.setCelly(rs.getString("celly"));
//
//			    	log.info("lac:"+entity.getLac()+",ci:"+entity.getCellid()
//			    			+",x:" + rs.getString("cellx")+",y:"+rs.getString("celly"));
//			    	
//			      } // end while
//			}
//		} catch (Exception ex) {
//			log.error("(Exception):", ex);
//		} finally {
//            try{
//          	  if(rs!=null){
//          		  rs.close(); 
//          	  }
//          	  if(pstmt!=null){
//          		  pstmt.close(); 
//          	  }
//          	  dbManager.freeConnection(conn);                   
//            }catch(Exception ex){
//          	  log.error("Error:",ex);
//            }
//		}
//	  return entity;
//  }
  


	/**
	 * 
	* package_name: cn.gz.zoomtech.common.db
	* file_name:    DbHandle.java
	* description: 白名单校验：
	* 号码属于定位企业，且号码定位状态是完全公开
	* 2013-11-26下午3:35:57
	* Author: chenhui
	 * @param msisdn 需要校验的号码
	 * @param userId CMS用户ID
	 * @return
	 */
   public boolean checkIsWhite(String msisdn,long userId){

	    Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;		
		boolean isWhite = false;
		try {
			
			dbManager = DBManager.getInstance();
			conn = dbManager.getConnection("DBPool");
			if (conn == null) {
				log.error("can't get connection DBPool");
				return isWhite;
			} else {
				String sql =  " select * from sp_white t " +
			       	 " where t.mdn='"+msisdn+"' and t.USER_ID="+userId+" and t.state=0"; // 隐私状态 0：完全公开；1：要我同意；2：拒绝定位。
				log.info("sql:" + sql);
				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
			    while (rs.next()) {
			    		isWhite = true;
			    		log.debug("msisdnIsWhite:" + msisdn);
			    		break;
			      } // end while
			}
		} catch (Exception ex) {
			log.error("(Exception):", ex);
		} finally {
          try{
        	  if(rs!=null){
        		  rs.close(); 
        	  }
        	  if(pstmt!=null){
        		  pstmt.close(); 
        	  }
        	  dbManager.freeConnection(conn);                   
          }catch(Exception ex){
        	  log.error("Error:",ex);
          }
		}
		log.info("mdn:" + msisdn+",isWhite:"+isWhite);
		return isWhite;
}
  
  /**
   * 
  * package_name: cn.gz.zoomtech.common.db
  * file_name:    DbHandle.java
  * description: 检查传入号码是否在系统黑名单中 
  * 2013-7-19下午5:09:24
  * Author: chenhui
   * @param msisdn
   * @return
   */
  public boolean checkIsBlack(String msisdn){

	    Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;		
		boolean isBlack = false;
		try {
			
			dbManager = DBManager.getInstance();
			conn = dbManager.getConnection("DBPool");
			if (conn == null) {
				log.error("can't get connection DBPool");
				return isBlack;
			} else {
				String sql =  " select * from black_mdn t " +
			       	 " where t.mdn='"+msisdn+"' ";
				 
				log.info("sql:" + sql);
				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
			    while (rs.next()) {
			    	isBlack = true;
			    		log.debug("msisdnIsBlack:" + msisdn);
			    		break;
			      } // end while
			}
		} catch (Exception ex) {
			log.error("(Exception):", ex);
		} finally {
        try{
      	  if(rs!=null){
      		  rs.close(); 
      	  }
      	  if(pstmt!=null){
      		  pstmt.close(); 
      	  }
      	  dbManager.freeConnection(conn);                   
        }catch(Exception ex){
      	  log.error("Error:",ex);
        }
		}
		log.info("mdn:" + msisdn+",isBlack:"+isBlack);
		return isBlack;
}
  
  /**
   * 
  * package_name: cn.gz.zoomtech.common.db
  * file_name:    DbHandle.java
  * description: 扫描基站数据，返回基站原始经纬度信息及优化后的扇区中心经纬度等
  * 2011-12-12上午11:05:58
  * Author: chenhui
   * @param lac 基站位置区标识
   * @param cellid 基站ID
   * @param type 1:扫t_cell_gsm_gx表；其它：扫t_cell_wcdma_gx表
   * @return
   */
//  public CellInfo scanCellXYBetter(String lac,String cellid,int type){
//	  
//	  
//	    CellInfo entity = null;
//	    Connection conn = null;
//		PreparedStatement pstmt = null;
//		ResultSet rs = null;
//		int count = 0;
//		try {
//			
//			dbManager = DBManager.getInstance();
//			conn = dbManager.getConnection("DBPool");
//			if (conn == null) {
//				log.error("can't get connection DBPool");
//				return null;
//			} else {
//				String sql = "";
//				if(type==1){
//					sql = " select * from t_cell_gsm_gx t " +
//			       	 " where t.cell_lac='"+lac+"' and t.cell_ci='"+cellid+"' ";
//				}else{
//					sql = " select * from t_cell_wcdma_gx t " +
//			       	 " where t.cell_lac='"+lac+"' and t.cell_ci='"+cellid+"' ";
//				}
//				
//				log.debug("sql:" + sql);
//				pstmt = conn.prepareStatement(sql);
//				rs = pstmt.executeQuery();
//			    while (rs.next()) {
//			    	count++;
//			    	if(count>=2){
//			    		break;
//			    	}
//			    	entity = new CellInfo();
//			    	entity.setLac(rs.getString("cell_lac"));
//			    	entity.setCellid(rs.getString("cell_ci"));
//			    	entity.setCellx(rs.getString("cellx"));
//			    	entity.setCelly(rs.getString("celly"));
//			    	entity.setCellCenterX(rs.getString("center_x"));
//			    	entity.setCellCenterY(rs.getString("center_y"));
////			    	entity.setCellr(rs.getString("cellR"));
//			    	
//			    	log.debug("lac:"+entity.getLac()+",ci:"+entity.getCellid()
//			    			+",x:" + rs.getString("cellx")+",y:"+rs.getString("celly")
//			    			+",centerX:"+entity.getCellCenterX()+",centerY:"+entity.getCellCenterY());
//			      } // end while
//			}
//		} catch (Exception ex) {
//			log.error("(Exception):", ex);
//		} finally {
//          try{
//        	  if(rs!=null){
//        		  rs.close(); 
//        	  }
//        	  if(pstmt!=null){
//        		  pstmt.close(); 
//        	  }
//        	  dbManager.freeConnection(conn);                   
//          }catch(Exception ex){
//        	  log.error("Error:",ex);
//          }
//		}
//	  return entity;
//}
  
  /**
   * 
  * package_name: cn.gz.zoomtech.common.db
  * file_name:    DbHandle.java
  * description: 返回查询基站最近距离等数据list，便于后续计算扇区中心数据
  * 2011-12-12上午11:29:07
  * Author: chenhui
   * @param type  1:扫t_cell_gsm_gx1表；其它：扫t_cell_wcdma_gx1表
   * @return
   */
//  private List<CellInfo> scanCellXYData(int type){
//	  
//	  
//	    CellInfo entity = null;
//	    Connection conn = null;
//		PreparedStatement pstmt = null;
//		ResultSet rs = null;
//		int count = 0;
//		List<CellInfo> rsList  = new ArrayList<CellInfo>();
//		
//		try {
//			
//			dbManager = DBManager.getInstance();
//			conn = dbManager.getConnection("DBPool");
//			if (conn == null) {
//				log.error("can't get connection DBPool");
//				return null;
//			} else {
//				String sql = "";
//				if(type==1){
//					sql = " select * from t_cell_gsm_gx1 t where t.neighbor_distince <> 'null' and t.neighbor_distince<>'0' ";
//				}else{
//					sql = " select * from t_cell_wcdma_gx1 t where t.neighbor_distince <> 'null' and t.neighbor_distince<>'0'";
//				}
//				
////				log.info("sql:" + sql);
//				pstmt = conn.prepareStatement(sql);
//				rs = pstmt.executeQuery();
//			    while (rs.next()) {
//			    	count++;
//			    	entity = new CellInfo();
//			    	entity.setLac(rs.getString("cell_lac"));
//			    	entity.setCellid(rs.getString("cell_ci"));
//			    	entity.setCellx(rs.getString("cellx"));
//			    	entity.setCelly(rs.getString("celly"));
//			    	entity.setCellCenterX(rs.getString("center_x"));
//			    	entity.setCellCenterY(rs.getString("center_y"));
//			    	entity.setCellr(rs.getString("cellR"));
//			    	entity.setNeighbor_distince(rs.getString("neighbor_distince"));
//			    	
////			    	log.info("lac:"+entity.getLac()+",ci:"+entity.getCellid()
////			    			+",x:" + rs.getString("cellx")+",y:"+rs.getString("celly")
////			    			+",centerX:"+entity.getCellCenterX()+",centerY:"+entity.getCellCenterY()+",dis:"+entity.getNeighbor_distince());
//			    	rsList.add(entity);
//			      } // end while
//			}
//		} catch (Exception ex) {
//			log.error("(Exception):", ex);
//		} finally {
//        try{
//      	  if(rs!=null){
//      		  rs.close(); 
//      	  }
//      	  if(pstmt!=null){
//      		  pstmt.close(); 
//      	  }
//      	  dbManager.freeConnection(conn); 
//      	  log.info("***rsList.size:"+rsList.size());
//        }catch(Exception ex){
//      	  log.error("Error:",ex);
//        }
//		}
//	  return rsList;
//}
  
  /**
   * 
  * package_name: cn.gz.zoomtech.common.db
  * file_name:    DbHandle.java
  * description: 查询方位角等基站数据，供本地整理数据用
  * 2011-12-13上午10:36:12
  * Author: chenhui
   * @param type  1:扫t_cell_gsm_gx1表；其它：扫t_cell_wcdma_gx1表
   * @return
   */
//  private List<CellInfo> scanCellFwjData(int type){
//	  
//	  
//	    CellInfo entity = null;
//	    Connection conn = null;
//		PreparedStatement pstmt = null;
//		ResultSet rs = null;
//		int count = 0;
//		List<CellInfo> rsList  = new ArrayList<CellInfo>();
//		
//		try {
//			
//			dbManager = DBManager.getInstance();
//			conn = dbManager.getConnection("DBPool");
//			if (conn == null) {
//				log.error("can't get connection DBPool");
//				return null;
//			} else {
//				String sql = ""; 
//				if(type==1){
//					sql = " select * from t_cell_gsm_gx1 t where t.antenna_fwj <> 'null' and t.cellr is not null  ";
//				}else{
//					sql = " select * from t_cell_wcdma_gx1 t where  t.antenna_fwj <> 'null' and t.cellr is not null ";
//				}
//				
////				log.info("sql:" + sql);
//				pstmt = conn.prepareStatement(sql);
//				rs = pstmt.executeQuery();
//			    while (rs.next()) {
//			    	count++;
//			    	entity = new CellInfo();
//			    	entity.setLac(rs.getString("cell_lac"));
//			    	entity.setCellid(rs.getString("cell_ci"));
//			    	entity.setCellx(rs.getString("cellx"));
//			    	entity.setCelly(rs.getString("celly"));
//			    	entity.setCellCenterX(rs.getString("center_x"));
//			    	entity.setCellCenterY(rs.getString("center_y"));
//			    	entity.setCellr(rs.getString("cellR"));
//			    	entity.setNeighbor_distince(rs.getString("neighbor_distince"));
//			    	entity.setAngle(rs.getString("antenna_fwj"));
////			    	if(rs.getString("cellx")==null||rs.getString("antenna_fwj")==null){
////			    	log.info("lac:"+entity.getLac()+",ci:"+entity.getCellid()
////			    			+",x:" + rs.getString("cellx")+",y:"+rs.getString("celly")
////			    			+",centerX:"+entity.getCellCenterX()+",centerY:"+entity.getCellCenterY()+",dis:"+entity.getNeighbor_distince());
////			    	continue;
////			    	}
//			    	rsList.add(entity);
//			      } // end while
//			}
//		} catch (Exception ex) {
//			log.error("(Exception):", ex);
//		} finally {
//      try{
//    	  if(rs!=null){
//    		  rs.close(); 
//    	  }
//    	  if(pstmt!=null){
//    		  pstmt.close(); 
//    	  }
//    	  dbManager.freeConnection(conn); 
//    	  log.info("***FWJ,rsList.size:"+rsList.size());
//      }catch(Exception ex){
//    	  log.error("Error:",ex);
//      }
//		}
//	  return rsList;
//}
  
  /**
   * 
  * package_name: cn.gz.zoomtech.common.db
  * file_name:    DbHandle.java
  * description: 更新扇区半径
  * 2011-12-12下午12:22:19
  * Author: chenhui
   * @param list 需要更新的list
   * @param type 1:更新t_cell_gsm_gx1表；其它：更新t_cell_wcdma_gx1表
   * @return
   */
//  private int updateCellR(List<CellInfo> list,int type){
//	  
//	    CellInfo entity = null;
//	    Connection conn = null;
//		PreparedStatement pstmt = null;
//		ResultSet rs = null;
//		int count = 0;
//		int update_count = 0;
//		String str_dis = null;
//		double d_dis= 0;
//		try {
//			
//			dbManager = DBManager.getInstance();
//			conn = dbManager.getConnection("DBPool");
//			if (conn == null) {
//				log.error("can't get connection DBPool");
//				return count;
//			} else {
//				String sql = "";
//				if(type==1){
//					sql = " update t_cell_gsm_gx1 t " 
//					    +  " set t.cellr=? "
//			       	    +  " where t.cell_lac=? and t.cell_ci=? ";
//				}else{						
//					sql = " update t_cell_wcdma_gx1 t " 
//					    +  " set t.cellr=? "
//			       	    +  " where t.cell_lac=? and t.cell_ci=?";
//				}
//				pstmt = conn.prepareStatement(sql);
//				for(int i=0;i<list.size();i++){
//					count++;
//					entity = list.get(i);
//					str_dis = entity.getNeighbor_distince();
//					d_dis = Double.valueOf(str_dis)/1.5;
//					
//					pstmt.setString(1, d_dis+"");
//					pstmt.setString(2, entity.getLac());
//					pstmt.setString(3, entity.getCellid());
//					
//					int updateResult = 0;
//					pstmt.addBatch();
//					if(i%500==0){
//						updateResult = pstmt.executeBatch().length;
//						update_count = update_count+updateResult;
//						log.info( i+"@@updateResult:" +updateResult );
////						pstmt.close();
//						
//					}
//					
//			
//				} // end for
//				int lastCount = pstmt.executeBatch().length;
//				log.info( "@@lastCount:" +lastCount );
//				update_count = update_count+lastCount;
//			}
//		} catch (Exception ex) {
//			log.error("(Exception):", ex);
//		} finally {
//        try{
//      	  if(rs!=null){
//      		  rs.close(); 
//      	  }
//      	  if(pstmt!=null){
//      		  pstmt.close(); 
//      	  }
//      	  dbManager.freeConnection(conn);                   
//        }catch(Exception ex){
//      	  log.error("Error:",ex);
//        }
//		}
//		log.info("***list count:"+count+",update_count:"+update_count);
//	  return update_count;
//}
//  
  
  
  /**
   * 
  * package_name: cn.zoomtech.ejsms.db
  * file_name:    DbHandle.java
  * description:  保存已发送数据,waiting...
  * 2011-6-1下午04:51:29
  * Author: chenhui
   * @param entity
   * @return
   */
//  public int saveEjSmsToSended(CellInfo entity){
//	  int result = 0 ;
//	  log.info("saveEjSendedListEntity:"+entity);
//	  if(null!=entity){
//		  Connection conn = null;
//			PreparedStatement pstmt = null;
//			try {
//				
//				dbManager = DBManager.getInstance();
//				conn = dbManager.getConnection("DBPool");
//				if (conn == null) {
//					log.error("can't get connection DBPool");
//					return result;
//				} else {				
//					String sql = " insert into T_SENDED_LIST_EJ(ID,BATCH_ID,SEND_ID,USER_ID, "
//						       + " SENDER,RECEIVER,MESSAGECONTENT,CREATE_TIME, "
//						       + " UPDATE_TIME,SEND_STATE,ISSEND,DESCRIPE, "
//						       + " MSG_TYPE "
//						       + " ) values( SEQ_SENDED_LIST_EJ_ID.nextval,?,?,?,"
//						       + "?,?,?,?," 
//						       + "sysdate,?,?,?,"
//						       + " ? )  ";
//					log.debug("sql:" + sql);
//					pstmt = conn.prepareStatement(sql);
//					
////					pstmt.setString(1, entity.getBatchId());
////					pstmt.setLong(2, entity.getSendId());
////					pstmt.setLong(3, entity.getUserId());
////					
////					pstmt.setString(4,entity.getSender());
////					pstmt.setString(5,entity.getReceiver());				
////					pstmt.setString(6,entity.getMessageContent());
////					pstmt.setTimestamp(7,entity.getCreateTime());
////					
////					pstmt.setInt(8,entity.getSendState());
////					pstmt.setInt(9,entity.getIsSend());
////					pstmt.setString(10,entity.getDescripe());
////					
////					pstmt.setInt(11,entity.getMsgType());
//					
//					result = pstmt.executeUpdate();
//				 	log.debug("dataID:"+entity.getCellid()+",saveResult:"+result);		   
//				}
//			} catch (Exception ex) {
//				log.error("(Exception):", ex);
//			} finally {
//	            try{	          	
//	          	  if(pstmt!=null){
//	          		  pstmt.close(); 
//	          	  }
//	          	  dbManager.freeConnection(conn);                   
//	            }catch(Exception ex){
//	          	  log.error("Error:",ex);
//	            }
//			}
//	  }else{
//		  log.warn("EjSendedListEntityIsNullWhenSave.");
//	  }
//	 
//	  return result;
//	  
//  }
  

  /**
   * 
  * package_name: cn.zoomtech.ejsms.db
  * file_name:    DbHandle.java
  * description: 测试SQL语句
  * 2011-6-1下午04:36:27
  * Author: chenhui
   * @param sql
   */
	public void testSql(String sql) {
	
		Connection conn = null;
		PreparedStatement pstmt = null;
		int rs = 0;		
		try {
			
			dbManager = DBManager.getInstance();
			conn = dbManager.getConnection("DBPool");
			if (conn == null) {
				log.error("can't get connection DBPool");
				return;
			} else {				
				log.info("sql:" + sql);
				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeUpdate();
			
			}
		} catch (Exception ex) {
			log.error("(testException):", ex);
		} finally {
              try{

            	  if(pstmt!=null){
            		  pstmt.close(); 
            	  }
            	  dbManager.freeConnection(conn);                   
              }catch(Exception ex){
            	  log.error("testError:",ex);
              }
              log.info("insertRow:"+rs);
		}
	}
	


	/**
	 * 
	* package_name: cn.zoomtech.ejsms.db
	* file_name:    DbHandle.java
	* description: SQL语句特殊字符转义
	* 2011-5-31下午11:33:25
	* Author: chenhui
	 * @param strSql
	 * @return
	 */
	public String encodeSql(String strSql) {
		if (strSql == null || strSql.length() == 0) {
			return "";
		}
		int len = 0;
		int i = 0;
		char c = ' ';
		strSql = strSql.trim();
		len = strSql.length();
		StringBuffer buf = new StringBuffer(strSql.length() + 6);
		for (i = 0; i < len; i++) {
			c = strSql.charAt(i);
			if (c == '\'') {
				buf.append("''");
			} else if (c == '%') {
				buf.append("%%");
			} else {
				buf.append(c);
			}
		}
		return buf.toString();
	}
	
	
	/**
	 * 
	* package_name: cn.gz.zoomtech.common.db
	* file_name:    DbHandle.java
	* description: SP 定位请求时SP鉴权
	* 2013-6-27下午2:05:31
	* Author: chenhui
	 * @param clientIp
	 * @param clientId
	 * @param password
	 * @return
	 */
	public String[] isSafeIp(String clientIp,String clientId,String password){
		  
        String results[] ={"false",null}; 
	    Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			
			dbManager = DBManager.getInstance();
			conn = dbManager.getConnection("DBPool");
			if (conn == null) {
				log.error("can't get connection DBPool");
				return results;
			} else {
				String sql = " select t.* from spsi_ip t where t.ip='"+clientIp+"'"
				           + " and t.username='"+clientId+"'"
				           + " and t.password='"+password+"'"
				           + " and t.state=0  ";
				log.info("sql:" + sql);
				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
			    while (rs.next()) {
			    	log.info("ip:"+rs.getString("ip")+",iptype:"+rs.getString("iptype"));
			    	results[0] = "true";
			    	results[1] =rs.getString("reportUrl");
                    break;
			      } // end while
			}
		} catch (Exception ex) {
			log.error("(Exception):", ex);
		} finally {
            try{
          	  if(rs!=null){
          		  rs.close(); 
          	  }
          	  if(pstmt!=null){
          		  pstmt.close(); 
          	  }
          	  dbManager.freeConnection(conn);                   
            }catch(Exception ex){
          	  log.error("Error:",ex);
            }
		}
	  return results;
  }
	
	
	public CmsUser queryCmsUserBySPipInfo(String clientId){
		  
		 
	    Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		CmsUser cmsUser = null;
		try {
			
			dbManager = DBManager.getInstance();
			conn = dbManager.getConnection("DBPool");
			if (conn == null) {
				log.error("can't get connection DBPool");
				return cmsUser;
			} else {
				String sql = " select t1.* from cms_user t1,spsi_ip t2 where t1.id = t2.user_id "
                           + " and t2.username = '"+clientId+"'";
				log.info("sql:" + sql);
				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
			    while (rs.next()) {
			    	cmsUser = new CmsUser();
			    	cmsUser.setReq_max(rs.getInt("req_max"));
			    	cmsUser.setReq_count(rs.getInt("req_count"));
			    	cmsUser.setId(rs.getLong("id"));
			    	cmsUser.setCheckBw(rs.getInt("checkBw"));
			    	cmsUser.setLogin_name(rs.getString("LOGIN_NAME"));
                    break;
			      } // end while
			}
		} catch (Exception ex) {
			log.error("(Exception):", ex);
		} finally {
            try{
          	  if(rs!=null){
          		  rs.close(); 
          	  }
          	  if(pstmt!=null){
          		  pstmt.close(); 
          	  }
          	  dbManager.freeConnection(conn);                   
            }catch(Exception ex){
          	  log.error("Error:",ex);
            }
		}
	  return cmsUser;
  }
	
	/**
	 * 
	* package_name: cn.gz.zoomtech.common.db
	* file_name:    DbHandle.java
	* description: 根据接口客户端访问账号，查找其对应的EC/SI用户【此用户可在CMS登录】
	* 2013-1-14上午1:42:08
	* Author: chenhui
	 * @param clientId
	 * @return
	 */
	public Long selectEcSiIdByClientId(String clientId){
		  
		 
	    Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Long  ecSiId = null;
		try {
			
			dbManager = DBManager.getInstance();
			conn = dbManager.getConnection("DBPool");
			if (conn == null) {
				log.error("can't get connection DBPool");
				return ecSiId;
			} else {
//				select su.user_id from sys_user su, t_cell_ips tcip ,sys_user_ips tui where tcip.clientid='gaode001'
//				         and tcip.id = tui.ips_id and tui.user_id = su.user_id
				         
				String sql = " select su.user_id from sys_user su, t_cell_ips tcip ,sys_user_ips tui where tcip.clientid='"+clientId+"'"
				           + " and tcip.id = tui.ips_id and tui.user_id = su.user_id ";
				log.info("sql:" + sql);
				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
			    while (rs.next()) {
			    	ecSiId = rs.getLong("user_id");
                    break;
			      } // end while
			}
		} catch (Exception ex) {
			log.error("(Exception):", ex);
		} finally {
            try{
          	  if(rs!=null){
          		  rs.close(); 
          	  }
          	  if(pstmt!=null){
          		  pstmt.close(); 
          	  }
          	  dbManager.freeConnection(conn);                   
            }catch(Exception ex){
          	  log.error("Error:",ex);
            }
		}
	  return ecSiId;
  }
	
	//*********** Table Fields
//	  ID          NUMBER not null,
//	  USER_ID     NUMBER not null,
//	  MDN         VARCHAR2(20),
//	  CREATE_TIME DATE default SYSDATE,
//	  RESP_RESULT NUMBER,
	
//	  RESP_CODE   VARCHAR2(10),
//	  RESP_MEMO   VARCHAR2(100),
//	  REQ_TIME    DATE default SYSDATE,
//	  TRANC_TIMES INTEGER,
//	  SERVER_CODE VARCHAR2(15),
	
//	  REQ_CODE    VARCHAR2(20),  主叫
//	  LAC         VARCHAR2(30),
//	  CI          VARCHAR2(30),
//	  JINGDU      VARCHAR2(30),
//	  WEIDU       VARCHAR2(30),
	
//	  RESPFROM    NUMBER
	public int saveLbsHistory(TLbsHistory entity){
		  int result = 0 ;
		  log.info("beforeSaveLbsHistory:"+entity);
		  if(null!=entity){
			  Connection conn = null;
				PreparedStatement pstmt = null;
				try {
					
					dbManager = DBManager.getInstance();
					conn = dbManager.getConnection("DBPool");
					if (conn == null) {
						log.error("can't get connection DBPool");
						return result;
					} else {				
						String sql = " insert into LBS_HISTORY(ID,USER_ID,MDN,CREATE_TIME,RESP_RESULT,"
							       + " RESP_CODE,RESP_MEMO,REQ_TIME,TRANC_TIMES,SERVER_CODE, "
							       + " REQ_CODE,LAC,CI,JINGDU,WEIDU,"
							       + " RESPFROM,reqType ) values( SEQ_LBS_HISTORY_ID.nextval,?,?,sysdate,?,"
							       + "?,?,?,?,?," 
							       + "?,?,?,?,?,"
							       + "?,? )  ";
						log.debug("sql:" + sql);
						pstmt = conn.prepareStatement(sql);
						
						pstmt.setLong(1, entity.getUserId());
						pstmt.setString(2, entity.getMdn());						
						pstmt.setByte(3, entity.getRespResult());
						
						pstmt.setString(4,entity.getRespCode());
						pstmt.setString(5,entity.getRespMemo());
						pstmt.setDate(6, DbHandle.getSqlDateTime(entity.getReqTime()));
						pstmt.setInt(7,entity.getTrancTimes());
						pstmt.setString(8,entity.getServerCode());

						pstmt.setString(9,entity.getOrid());
						pstmt.setString(10,entity.getCellLac());
						pstmt.setString(11,entity.getCellCi());
						pstmt.setString(12,entity.getJindu());
						pstmt.setString(13,entity.getWeidu());
						
						pstmt.setInt(14,entity.getRespForm());
						pstmt.setString(15,entity.getReqType());
						result = pstmt.executeUpdate();
					 	log.debug("userID:"+entity.getUserId()+",saveResult:"+result);		   
					}
				} catch (Exception ex) {
					log.error("(Exception):", ex);
				} finally {
		            try{	          	
		          	  if(pstmt!=null){
		          		  pstmt.close(); 
		          	  }
		          	  dbManager.freeConnection(conn);                   
		            }catch(Exception ex){
		          	  log.error("Error:",ex);
		            }
				}
		  }else{
			  log.warn("TLbsHistoryIsNullWhenSave.");
		  }
		 
		  return result;
		  
	  }
	
	/**
	 * 
	* package_name: cn.gz.zoomtech.common.db
	* file_name:    DbHandle.java
	* description: 将定位企业的当日定位数量置为0
	* 2013-8-11下午4:41:40
	* Author: chenhui
	 * @return
	 */
	public int updateCmsUserReqCountToZero(){
		  int result = 0 ;
			  Connection conn = null;
			  PreparedStatement pstmt = null;
				try {
					
					dbManager = DBManager.getInstance();
					conn = dbManager.getConnection("DBPool");
					if (conn == null) {
						log.error("can't get connection DBPool");
						return result;
					} else {				
						String sql = " update cms_user t set t.req_count = 0 where t.usertype = 2 ";
						log.debug("sql:" + sql);
						pstmt = conn.prepareStatement(sql);

						result = pstmt.executeUpdate();
					 	log.debug("updateCmsUserReqCountSPNumbers:"+result);		   
					}
				} catch (Exception ex) {
					log.error("updateCmsUserReqCountError:", ex);
				} finally {
		            try{	          	
		          	  if(pstmt!=null){
		          		  pstmt.close(); 
		          	  }
		          	  dbManager.freeConnection(conn);                   
		            }catch(Exception ex){
		          	  log.error("Error:",ex);
		            }
				}

		  return result;
		  
	  }
	
	
	/**
	 * 
	* package_name: cn.gz.zoomtech.common.db
	* file_name:    DbHandle.java
	* description: 更新SP当前请求次数(同步锁)
	* 2013-8-11下午5:24:40
	* Author: chenhui
	 * @return
	 */
	public synchronized int addCmsUserReqCount(CmsUser cmsUser){
		  int result = 0 ;
			  Connection conn = null;
			  PreparedStatement pstmt = null;
				try {
					
					dbManager = DBManager.getInstance();
					conn = dbManager.getConnection("DBPool");
					if (conn == null) {
						log.error("can't get connection DBPool");
						return result;
					} else {				
						String sql = " update cms_user t set t.req_count = (t.req_count+1) where t.id = ? ";
						log.debug("sql:" + sql);
						pstmt = conn.prepareStatement(sql);
						pstmt.setLong(1,cmsUser.getId());
						result = pstmt.executeUpdate();
					 	log.debug("addCmsUserReqCount:"+result);		   
					}
				} catch (Exception ex) {
					log.error("addCmsUserReqCountError:", ex);
				} finally {
		            try{	          	
		          	  if(pstmt!=null){
		          		  pstmt.close(); 
		          	  }
		          	  dbManager.freeConnection(conn);                   
		            }catch(Exception ex){
		          	  log.error("Error:",ex);
		            }
				}

		  return result;
		  
	  }
	
	/**
	 * 
	* package_name: cn.gz.zoomtech.common.db
	* file_name:    DbHandle.java
	* description: 查询号段数据
	* 2013-10-4上午3:24:08
	* Author: chenhui
	 */
	public void queryPhoneSection(){
		  
			  Connection conn = null;
			  PreparedStatement pstmt = null;
			  ResultSet rs = null;
				try {
					
					dbManager = DBManager.getInstance();
					conn = dbManager.getConnection("DBPool");
					if (conn == null) {
						log.error("can't get connection DBPool");
						 
					} else {				
						String sql = " select t1.section_value,t1.section_name,t1.carrier from phone_section t1 where t1.section_name like '安徽%' and t1.carrier='1' ";
						log.debug("sql:" + sql);
						pstmt = conn.prepareStatement(sql);

						rs = pstmt.executeQuery();
						while(rs.next()){
							log.info("号段信息:"+rs.getString("section_value")+","+rs.getString("section_name")+","+rs.getString("carrier"));
						}
					 	 		   
					}
				} catch (Exception ex) {
					log.error("queryPhoneSectionError:", ex);
				} finally {
		            try{	          	
		          	  if(pstmt!=null){
		          		  pstmt.close(); 
		          	  }
		          	  dbManager.freeConnection(conn);                   
		            }catch(Exception ex){
		          	  log.error("Error:",ex);
		            }
				}
 
	  }
	
	/**
	 * 
	* package_name: cn.gz.zoomtech.common.db
	* file_name:    DbHandle.java
	* description: 判断被定位号码是否为安徽联通号段
	* 2013-10-4上午5:30:04
	* Author: chenhui
	 * @param tel
	 * @return true:合法号段； false:非法号段
	 */
	public boolean isUnicomPhoneSection(String tel){
		  boolean isUnicomPhone = false;
		  Connection conn = null;
		  PreparedStatement pstmt = null;
		  ResultSet rs = null;
			try {
				
				dbManager = DBManager.getInstance();
				conn = dbManager.getConnection("DBPool");
				if (conn == null) {
					log.error("can't get connection DBPool");
					 
				} else {				
					String sql = " select t.SECTION_NAME from phone_section t where t.section_value=substr('"+tel+"',0,7)";
					pstmt = conn.prepareStatement(sql);
					rs = pstmt.executeQuery();
					while(rs.next()){
						isUnicomPhone = true;
						log.info("tel:"+tel+",itSectionName:"+rs.getString("SECTION_NAME"));
						break;
					}
				 	 		   
				}
			} catch (Exception ex) {
				log.error("queryPhoneSectionError:", ex);
			} finally {
	            try{	          	
	          	  if(pstmt!=null){
	          		  pstmt.close(); 
	          	  }
	          	  dbManager.freeConnection(conn);                   
	            }catch(Exception ex){
	          	  log.error("Error:",ex);
	            }
			}
			return isUnicomPhone;

}

	
  
}
